# 导包
import pymysql

# 创建类
class SQLUtil():
    __conn = None
    __cursor = None

    # 类方法：创建连接
    @classmethod
    def __getConn(cls):
        # 数据库：estudy_db; kaikela_mem; kaikela_support; bigdata; estudy_cpcore; estudy_recruit; estudy_class; wechat;
        SQL_data = ["10.0.0.233", 3502, "xhx_user", "A654321", "kaikela_mem"]
        if cls.__conn is None:
            cls.__conn = pymysql.connect(host=SQL_data[0], port=SQL_data[1], user=SQL_data[2], password=SQL_data[3],
                                         database=SQL_data[4])
        return cls.__conn

    # 创建游标
    @classmethod
    def __getCursor(cls):
        if cls.__cursor is None:
            cls.__cursor = cls.__getConn().cursor()
        return cls.__cursor

    # 执行sql
    @classmethod
    def executeSQL(cls, sql):
        try:
            cursor = cls.__getCursor()
            cursor.execute(sql)
            if sql.split()[0].lower() == "select":

                # 获取表头（列名）
                headers = [desc[0] for desc in cursor.description]
                # 获取查询结果
                results = cursor.fetchall()

                # 打印表头和结果
                print(headers, end='\n')  # 表头不换行
                print(results)
                # print(cursor.fetchall())
                return cursor.fetchall()
            else:
                cls.__getConn().commit()
                print("影响的行：{}".format(cursor.rowcount))
                return cursor.rowcount

        except Exception as e:
            cls.__getConn().rollback()
            print(f"Error:{e}")

        finally:
            cls.__closeCursor()
            cls.__closeConn()

    # 关闭游标
    @classmethod
    def __closeCursor(cls):
        if cls.__cursor:
            cls.__cursor.close()
            cls.__cursor = None

    # 关闭链接
    @classmethod
    def __closeConn(cls):
        if cls.__conn:
            cls.__conn.close()
            cls.__conn = None

sql = "select * from mb_member where mobile_no = 16000000002;"
sql2 = "update mb_member set name = '二娃' where mobile_no = 16000000002;"
sql3 = "select t1.id,t2.member_id from estudy_class.cs_communicate_record t1 join kaikela_mem.mb_member t2 on t1.member_id = t2.member_id where t1.member_id = '202506260106262094';"
SQLUtil().executeSQL(sql3)